package icu.groot.alan.dao.impl;

import icu.groot.alan.dao.ReportDao;
import icu.groot.alan.entity.Report;
import icu.groot.alan.util.Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ReportDaoImpl implements ReportDao {
    @Override
    public int add(Connection connection, int userCode, int announceId, double temperature, String oneselfHealth, String oneselfHealthDetails, String route, String address, String familyHealth, String familyHealthDetails, String anomaly, long reportTime) throws SQLException {
        PreparedStatement preparedStatement = null;
        Object[] params = {announceId, userCode, temperature, oneselfHealth, oneselfHealthDetails, route, address, familyHealth, familyHealthDetails, anomaly, reportTime};
        String sql = "insert into report (announce_id, user_code, temperature, oneself_health, oneself_health_details, route, address, family_health, family_health_details, anomaly, report_time) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        int updateRow = Dao.execute(connection, preparedStatement, sql, params);
        Dao.closeResource(preparedStatement);
        return updateRow;
    }

    @Override
    public int checkAdd(Connection connection, int userCode, int announceId) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int count = 0;
        Object[] params = {userCode, announceId};
        String sql = "select count(*) count from report where user_code = ? and announce_id = ?";
        resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params);
        while (resultSet.next()) {
            count = resultSet.getInt("count");
        }
        Dao.closeResource(preparedStatement, resultSet);
        return count;
    }

    @Override
    public List<Report> getReportByAnnounceId(Connection connection, int announceId) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Object[] params = {announceId};
        List<Report> reports = new ArrayList<>();
        Report report = null;
//        String sql = "select r.user_code user_code, r.oneself_health oneself_health, r.oneself_health_details oneself_health_details, r.address address, r.route route, r.family_health family_health, r.family_health_details family_health_details, r.temperature temperature, r.anomaly anomaly, u.user_name user_name, d.name departmentName from report r left join `user` u on r.user_code = u.code left join department d on u.department_id = d.id where announce_id = ?";
        String sql = "\n" +
                "SELECT * FROM\n" +
                "(\n" +
                "SELECT\n" +
                "\tu.`code` `code`,\n" +
                "\tu.user_name user_name,\n" +
                "\td.`name` department_name\n" +
                "FROM\n" +
                "\t`user` u LEFT JOIN department d ON u.department_id = d.id\n" +
                ") u LEFT JOIN (\n" +
                "SELECT \n" +
                "\ta.id announce_id,\n" +
                "\tr.user_code user_code,\n" +
                "\tr.oneself_health oneself_health,\n" +
                "\tr.oneself_health_details oneself_health_details,\n" +
                "\tr.address address,\n" +
                "\tr.route route,\n" +
                "\tr.family_health family_health,\n" +
                "\tr.family_health_details family_health_details,\n" +
                "\tr.temperature temperature,\n" +
                "\tr.anomaly anomaly\n" +
                "FROM announce a LEFT JOIN report r ON a.id = r.announce_id WHERE r.announce_id  = ?\n" +
                ") r ON u.`code` = r.user_code";
        resultSet = Dao.execute(connection, preparedStatement, resultSet, sql, params);
        while (resultSet.next()) {
            report = new Report();
            report.setUserCode(resultSet.getInt("code"));
            report.setOneselfHealth(resultSet.getString("oneself_health"));
            report.setOneselfHealthDetails(resultSet.getString("oneself_health_details"));
            report.setAddress(resultSet.getString("address"));
            report.setRouter(resultSet.getString("route"));
            report.setFamilyHealth(resultSet.getString("family_health"));
            report.setFamilyHealthDetails(resultSet.getString("family_health_details"));
            report.setDepartment(resultSet.getString("department_name"));
            report.setTemperature(resultSet.getString("temperature"));
            report.setAnomaly(resultSet.getString("anomaly"));
            report.setUserName(resultSet.getString("user_name"));
            reports.add(report);
        }
        Dao.closeResource(preparedStatement, resultSet);
        return reports;

    }
}
